﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace ScoreManage
{
    public partial class GradeStatistic : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnByStudent_Click(object sender, EventArgs e)
        {
            GridView1.DataSource = GetDataTable1();
            GridView1.DataBind();
        }

        DataTable GetDataTable1()
        {
            string strConn = ConfigurationManager.ConnectionStrings["studbConnectionString"].ConnectionString;
            SqlConnection conn = new SqlConnection(strConn);
            conn.Open();
            string strSql = string.Format("SELECT sno 学号, sname 姓名, SUM(credit*score)/SUM(credit) 均绩, SUM(credit) 学分 FROM scview GROUP BY sno, sname ORDER BY 均绩 DESC");
            SqlCommand myCommand = new SqlCommand(strSql, conn);
            SqlDataReader dr1 = myCommand.ExecuteReader();

            DataTable dt = new DataTable("cart");
            DataColumn dc1 = new DataColumn("学号", Type.GetType("System.String"));
            DataColumn dc2 = new DataColumn("姓名", Type.GetType("System.String"));
            DataColumn dc3 = new DataColumn("均绩", Type.GetType("System.String"));
            DataColumn dc4 = new DataColumn("学分", Type.GetType("System.String"));
            dt.Columns.Add(dc1);
            dt.Columns.Add(dc2);
            dt.Columns.Add(dc3);
            dt.Columns.Add(dc4);
            //以上代码完成了DataTable的构架，但是里面是没有任何数据的
            while (dr1.Read())
            {
                DataRow dr = dt.NewRow();
                dr["学号"] = dr1["学号"].ToString();
                dr["姓名"] = dr1["姓名"].ToString();
                dr["均绩"] = dr1["均绩"].ToString();
                dr["学分"] = dr1["学分"].ToString();
                dt.Rows.Add(dr);
            }
            return dt;
        }

        protected void btnByCourse_Click(object sender, EventArgs e)
        {
            GridView1.DataSource = GetDataTable2();
            GridView1.DataBind();
        }

        DataTable GetDataTable2()
        {
            string strConn = ConfigurationManager.ConnectionStrings["studbConnectionString"].ConnectionString;
            SqlConnection conn = new SqlConnection(strConn);
            conn.Open();
            string strSql = string.Format("SELECT cno 课程号, cname 课程名, AVG(score) 平均成绩 FROM scview GROUP BY cno, cname ORDER BY 平均成绩 DESC");
            SqlCommand myCommand = new SqlCommand(strSql, conn);
            SqlDataReader dr1 = myCommand.ExecuteReader();

            DataTable dt = new DataTable("cart");
            DataColumn dc1 = new DataColumn("课程号", Type.GetType("System.String"));
            DataColumn dc2 = new DataColumn("课程名", Type.GetType("System.String"));
            DataColumn dc3 = new DataColumn("平均成绩", Type.GetType("System.String"));
            dt.Columns.Add(dc1);
            dt.Columns.Add(dc2);
            dt.Columns.Add(dc3);
            //以上代码完成了DataTable的构架，但是里面是没有任何数据的
            while (dr1.Read())
            {
                DataRow dr = dt.NewRow();
                dr["课程号"] = dr1["课程号"].ToString();
                dr["课程名"] = dr1["课程名"].ToString();
                dr["平均成绩"] = dr1["平均成绩"].ToString();
                dt.Rows.Add(dr);
            }
            return dt;
        }

        protected void btnReturn_Click(object sender, EventArgs e)
        {
            if (Session["Type"].ToString() == "学生")
            {
                Response.Redirect("StudentGradeQuery.aspx?item=4");
            }
            else if (Session["Type"].ToString() == "教师")
            {
                Response.Redirect("TeacherGradeManage.aspx");
            }
            else if (Session["Type"].ToString() == "用户")
            {
                Response.Redirect("CourseManage.aspx");
            }
        }
    }
}